Exercise

Q1.Extract the “Airport Passengers” table into R with this code.

passengers <- read_xls("WebAirport_FY_1986-2017.xls", sheet=3, skip=6)
head(passengers)
## # A tibble: 6 x 14
##                     X__1         AIRPORT    Year  Rank  INBOUND OUTBOUND
##                    <chr>           <chr>   <chr> <chr>    <dbl>    <dbl>
## 1 TOTAL AUSTRALIA1985-86 TOTAL AUSTRALIA 1985-86     - 14543928 14543928
## 2 TOTAL AUSTRALIA1986-87 TOTAL AUSTRALIA 1986-87     - 15001877 15001877
## 3 TOTAL AUSTRALIA1987-88 TOTAL AUSTRALIA 1987-88     - 16181355 16181355
## 4 TOTAL AUSTRALIA1988-89 TOTAL AUSTRALIA 1988-89     - 16497749 16497826
## 5 TOTAL AUSTRALIA1989-90 TOTAL AUSTRALIA 1989-90     - 11861519 11861644
## 6 TOTAL AUSTRALIA1990-91 TOTAL AUSTRALIA 1990-91     - 16536767 16536767
## # ... with 8 more variables: TOTAL <dbl>, INBOUND__1 <dbl>,
## #   OUTBOUND__1 <dbl>, TOTAL__1 <dbl>, INBOUND__2 <dbl>,
## #   OUTBOUND__2 <dbl>, TOTAL__2 <dbl>, X__2 <chr>

Q2 .Tidy the data, to produce a data set with these columns

-airport: all of the airports. (You can remove the TOTAL, because this can be easily recalculated as needed.)

-year

-type_of_flight: DOMESTIC, INTERNATIONAL

-bound: IN or OUT

passengers_tidy<-passengers%>%
  select(AIRPORT,Year)%>%
  mutate(DOMESTIC_INBOUND = passengers$INBOUND, DOMESTIC_OUTBOUND = passengers$OUTBOUND, INTERNATIONAL_INBOUND = passengers$INBOUND__1, INTERNATIONAL_OUTBOUND = passengers$OUTBOUND__1)%>%
  gather(variable, count, -AIRPORT, -Year)%>%
  separate(variable, c("type_of_flight","bound"))
passengers_tidy<-passengers_tidy%>%
  filter(AIRPORT!="TOTAL AUSTRALIA")
head(passengers_tidy)
## # A tibble: 6 x 5
##    AIRPORT    Year type_of_flight   bound   count
##      <chr>   <chr>          <chr>   <chr>   <dbl>
## 1 ADELAIDE 1985-86       DOMESTIC INBOUND  984010
## 2 ADELAIDE 1986-87       DOMESTIC INBOUND  973288
## 3 ADELAIDE 1987-88       DOMESTIC INBOUND 1047798
## 4 ADELAIDE 1988-89       DOMESTIC INBOUND 1075380
## 5 ADELAIDE 1989-90       DOMESTIC INBOUND  823571
## 6 ADELAIDE 1990-91       DOMESTIC INBOUND 1136965

Q3.Use your data to make

-a barchart of type of flight facetted by year.

-a scatterplot of domestic inbound vs outbound passengers facetted by year. There are three airports with much higher numbers, which are these three?

-a scatterplot of international inbound vs outbound passengers facetted by year

p1 <- ggplot(passengers_tidy, aes(x=Year, fill=type_of_flight, label=paste(count))) +
  geom_bar(aes(weight=count)) + 
  theme(axis.text.x = element_text(angle = 90),
        axis.title = element_text(colour = "blue",size = 12),
        axis.text=element_text(size=8)) +
  xlab("Year") + 
  ylab("Pessengers (ten of millions)")+
  scale_y_continuous(labels = c("0"="0","50000000"="5","100000000"="10","150000000"="15"))+
  scale_x_discrete(labels = c("1985-86"="85-86","1986-87"="86-87","1987-88"="87-88","1988-89"="88-89","1989-90"="89-90","1990-91"="90-91","1991-92"="91-92","1992-93"="92-93","1993-94"="93-94","1994-95"="94-95","1995-96"="95-96","1996-97"="96-97","1997-98"="97-98","1998-99"="98-99","1999-00"="99-00","2000-01"="00-01","2001-02"="01-02","2002-03"="02-03","2003-04"="03-04","2004-05"="04-05","2005-06"="05-06","2006-07"="06-07","2007-08"="07-08","2008-09"="08-09","2009-10"="09-10","2010-11"="10-11","2011-12"="11-12","2012-13"="12-13","2013-14"="13-14","2014-15"="14-15","2015-16"="15-16","2016-17"="16-17"))
ggplotly(p1)
DOMESTIC<-passengers%>%select(AIRPORT,Year,INBOUND,OUTBOUND)
DOMESTIC<-passengers%>%filter(AIRPORT!="TOTAL AUSTRALIA")
p2<-ggplot(DOMESTIC, aes( x = INBOUND, y = OUTBOUND, label = AIRPORT ))+
  geom_point()+
  facet_wrap(~Year, ncol =8)+
  ggtitle("Inbound Demostic Pessengers vs Outbound")+
  theme(axis.title = element_text(colour = "blue",size = 12),
        axis.text=element_text(size=8))+
  xlab("Inbound (millions)")+
  ylab("Outbound (millions)")+
  scale_y_continuous(breaks = c(0,5000000,10000000), labels = c("0","5","10"))+
  scale_x_continuous(breaks = c(0,5000000,10000000), labels = c("0","5","10"))
ggplotly(p2)

The three airport with higest passengers are SYDNEY, MELBOURNE and BRISBANE.

INTERNATIONAL<-passengers%>%select(AIRPORT,Year,INBOUND__1,OUTBOUND__1)
INTERNATIONAL<-passengers%>%filter(AIRPORT!="TOTAL AUSTRALIA")
p3<-ggplot(INTERNATIONAL, aes( x = INBOUND__1, y = OUTBOUND__1, label = AIRPORT))+
  geom_point()+
  facet_wrap(~Year, ncol =8)+
  ggtitle("Inbound International Pessengers vs Outbound")+
  xlab("Inbound (millions)")+
  ylab("Outbound (millions)")+
  theme(axis.title = element_text(colour = "blue",size = 12),
        axis.text=element_text(size=8))+
  scale_y_continuous(labels = c("0"="0","2000000"="2","4000000"="4","6000000"="6","8000000"="8"))+
  scale_x_continuous(labels = c("0"="0","2000000"="2","4000000"="4","6000000"="6","8000000"="8"))
ggplotly(p3)

Q4.Write a paragraph explaining what you learn from each of the plots

From graph 1 we find that there are domestic passengers versue international passengers and the number of both domestic and international passengers were increasing between 1985 to 2017. From graph 2 and 3,both of them show that the amount of inbound passengers versus the outbound are similar in each airport. There is a strong, positive linear correlation between the domestic inbound and outbound flights. This also applies for the international inbound and outbound flights. For example, an airport with higher inbound passengers would have a higher number in outbound passengers. The reason might be the passengers normally purchase the inbound flight and outbound flight in the same airport for convenience. Also both of the top 3 airport with higerst passengers is SYDNEY, MELBOURNE and BRISBANE. But the difference is that SYDNEY and MELBOURNE airport has the similar number of passengers in domestic but SYDNEY get much more international passengers during 1985~2017.